package com.fzg.common.tool.xmlToExcel;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jdom.Document;
import org.jdom.Element;
import org.jdom.input.SAXBuilder;

import java.io.*;
import java.util.*;

/**
 * create by fzg
 * 2022/11/11 10:44
 * xml格式的数据转为Excel文件的数据
 */


public class XmlToExcel {

    public static void main(String[] args) {
        xmlToExcel("E:\\学习\\项目\\vue-vant-app\\myBlog\\myCaveolae_server\\src\\main\\resources\\static\\people.xml","poople");
    }


        /**
         * @description: xml文件转为Excel文件
         * @param xmlPosition (xml文件的位置)
         * @param excelName （默认生成扩展名为xls的文件，默认位置生成在桌面）
         */
        public static void xmlToExcel(String xmlPosition,String excelName){
            //1.创建SAXBuilder对象
            SAXBuilder saxBuilder = new SAXBuilder();
            //2.创建输入流
            InputStream is;
            try {
                is = new FileInputStream(xmlPosition);
            }catch (Exception e){
                e.printStackTrace();
                System.out.println("文件位置未找到！");
                return;
            }

            //3.将输入流加载到build中
            Document document = null;
            try {
                document = saxBuilder.build(is);
            }catch (Exception e){
                e.printStackTrace();
                System.out.println("文件内容构建失败！");
                System.out.println("请检查文件内容是否符合xml格式");
            }
            //4.获取根节点
            Element rootElement = document.getRootElement();
            //5.获取子节点
            List<Element> children = rootElement.getChildren();
            ArrayList<HashMap<String, Object>> list = new ArrayList<>();
            for (Element child : children) {
                // System.out.println("通过rollno获取属性值:"+child.getAttribute("属性名"));
                // List<Attribute> attributes = child.getAttributes();
                //打印属性
                // for (Attribute attr : attributes) {
                // System.out.println(attr.getName()+":"+attr.getValue());
                // }
                // 标签的属性，可以没有

                List<Element> childrenList = child.getChildren();
                HashMap<String, Object> map = new HashMap<>();
                for (Element o : childrenList) {
                    // System.out.println("节点名:"+o.getName()+"---"+"节点值:"+o.getValue());
                    map.put(o.getName(), o.getValue());
                }
                list.add(map);
            }
             // System.out.println(list);
            if (list.size() > 0){
                // 新建excel文件（如果存在就提示换个名字）
                File file = new File("C:\\Users\\Asus\\Desktop\\" + excelName + ".xls");
                if (file.exists()){
                    System.out.println("该位置存在相同名字的文件，请重新命名！");
                }else {
                    try {
                        boolean newFile = file.createNewFile();
                        if (newFile){
                            System.out.println("文件创建成功！");
                            System.out.println("开始导入数据！");
                            HSSFWorkbook wb = new HSSFWorkbook();
                            HSSFSheet sheet = wb.createSheet("sheet1");
                            for (int i = 0; i <= list.size(); i++) {
                                HSSFRow row = sheet.createRow(i);
                                if (i == 0){
                                    Set<Map.Entry<String, Object>> entries = list.get(i).entrySet();
                                    int j = 0;
                                    for (Map.Entry<String, Object> entry : entries) {
                                        HSSFCell cell = row.createCell(j);
                                        cell.setCellValue(entry.getKey());
                                        j++;
                                    }
                                }else {
                                    Set<Map.Entry<String, Object>> entries = list.get(i-1).entrySet();
                                    int j = 0;
                                    for (Map.Entry<String, Object> entry : entries) {
                                        HSSFCell cell = row.createCell(j);
                                        cell.setCellValue(entry.getValue().toString());
                                        j++;
                                    }
                                }
                            }
                            ByteArrayOutputStream os = new ByteArrayOutputStream();
                            try {
                                wb.write(os);
                            }catch (IOException e){
                                System.out.println("导入失败！");
                                e.printStackTrace();
                            }
                            byte[] content = os.toByteArray();
                            OutputStream fos;
                            try {
                                fos = new FileOutputStream(file);
                                fos.write(content);
                                os.close();
                                fos.close();
                                System.out.println("数据导入成功！");
                            }catch (IOException e){
                                System.out.println("导入失败！");
                                e.printStackTrace();
                            }
                        }else {
                            System.out.println("文件创建失败！");
                        }
                    } catch (IOException e) {
                        e.printStackTrace();
                        System.out.println("excel文件创建失败！");
                    }
                }
            }else {
                System.out.println("源文件没有数据！");
            }

    }



    public Workbook readExcel(InputStream is, String extString){
        Workbook wb = null;
        extString = extString.substring(extString.lastIndexOf("."));
        try {
            if(".xls".equals(extString)){
                return new HSSFWorkbook(is);
            }else if(".xlsx".equals(extString)){
                return new XSSFWorkbook(is);
            }else {
                return null;
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }


}
